# 数据库的工具类
import pymysql


class DBUtil:
    # 定义类属性
    conn = None

    # 封装创建数据库链接的方法,由于调用方不关心是如何连接数据库的，所以对外隐藏掉
    @classmethod
    def __connect_db(cls):
        cls.conn = pymysql.connect(host="211.103.136.244", port=7068, user="student", password="KDTX_student_202304",
                                   database="huike", charset="utf8")
        return cls.conn

    # 封装关闭数据库链接的方法
    @classmethod
    def __close_db(cls):
        cls.conn.close()
        pass

    # 封装查询方法
    @classmethod
    def query_db(cls, sql):
        cursor = None
        res = None
        try:
            # 创建数据库的链接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql,return 结果集
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as error:
            print("读取数据库报错：", error)
            pass
        finally:
            # 关闭游标
            cursor.close()
            # 关闭链接
            cls.__close_db()
            return res

        pass

    # 增删改方法
    @classmethod
    def update_db(cls, sql):
        cursor =None
        try:
            # 创建数据库的链接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql
            cursor.execute(sql)
            # 打印一下影响数据库的行数
            rows = cls.conn.affected_rows()
            print("影响数据库的行数：", rows)
            # 事务的提交
            cls.conn.commit()
        except Exception as error:
            print("更新数据库报错：",error)
            #事务的回滚
            cls.conn.rollback()
            pass
        finally:
            # 关闭游标
            cursor.close()
            # 关闭链接
            cls.__close_db()



        pass

    pass


if __name__ == '__main__':
    # print(DBUtil.query_db("select * from t_book;"))

    insert_sql = "insert into t_book (id,title,pub_date) values(10022,'三国志','1999-09-09');"
    update_sql = "update t_book set comment = 250 where id =  10022;"
    query_sql ="select * from t_book tb where id =10022;"
    print(DBUtil.query_db(query_sql))

    # DBUtil.update_db(update_sql)

